Imports System.Data.OleDb
Imports RIBS.Code.DBCode
Imports RIBS.Code.GlobalModule1

Public Class RibsDataImportClass
    Function DataImport(ByVal dt As DataTable) As DataTable
        Dim connection As New OleDbConnection(gConnectionString)
        Dim command As New OleDbCommand()
        Try
            Dim iCtr As Int16 = 0
            Dim SQL As String = ""
            Dim RunNumber As Int32 = 0
            Dim LETTER_CODE As String = ""
            Dim oSetting As New SystemSetting
            Dim ValidCodes As String = oSetting.ImportTypes.ToUpper

            'Using connection As New OleDbConnection(gConnectionString)
            'Dim transaction As OleDbTransaction

            Dim LetterCodes As String = GetLetterCodes()
            Dim oRibs As New RibsClass
            'Dim PremAmount As Double = 0
            Dim dr As DataRow
            'Dim drErr As DataRow
            Dim dt2 As New DataTable
            Dim daCol As DataColumn

            Dim bErrorFlag As Boolean = False

            Dim SQLValues As String = ""

            ProgressBarMaxValue = dt.Rows.Count
            iCtr = 0
            Dim dc = New DataColumn("Import Status", GetType(String), Nothing, System.Data.MappingType.Element)
            dt.Columns.Add(dc)
            connection.Open()
            For Each dr In dt.Rows
                command.Connection = connection
                iCtr += 1
                ProgressBarUpdate = iCtr
                Application.DoEvents()
                SQL = "INSERT INTO RIBS ("
                SQLValues = " VALUES ("
                bErrorFlag = False
                For Each daCol In dt.Columns

                    Select Case daCol.ColumnName
                        Case "_RECORD_ID"
                            'If IsNumeric(dr(daCol.Ordinal)) Then
                            'Debug.Print(dr(daCol.Ordinal).ToString)
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                            'Else
                            'bErrorFlag = True
                            'End If
                        Case "LETTER_CODE"
                            If InStr(LetterCodes, dr(daCol.Ordinal).ToString & ",") = 0 Then
                                CheckLetterCode(dr(daCol.Ordinal).ToString)
                            End If
                            LETTER_CODE = dr(daCol.Ordinal).ToString.Trim
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "CURRENT_RUN_NUMBER"
                            If IsNumeric(dr(daCol.Ordinal)) Then
                                SQLValues += " " & dr(daCol.Ordinal).ToString & ","
                            Else
                                bErrorFlag = True
                            End If
                        Case "SYSTEM"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "AMOUNT_DUE"
                            If IsNumeric(Replace(dr(daCol.Ordinal).ToString, "$", "")) Then
                                'PremAmount = FuncCalc(dr("PAYMENT_FREQUENCY"), CDbl(Replace(Replace(dr(daCol.Ordinal).ToString, "$", ""), ",", "")))
                                SQLValues += " " & dr(daCol.Ordinal).ToString & ","
                            Else
                                bErrorFlag = True
                            End If
                        Case "POLICY_NUMBER"
                            If IsNumeric(dr(daCol.Ordinal)) Then
                                SQLValues += " " & dr(daCol.Ordinal).ToString & ","
                            Else
                                bErrorFlag = True
                            End If
                        Case "PRODUCT_TYPE"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_NAME"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_ADDRESS_1"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_ADDRESS_2"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_ADDRESS_3"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_SUBURB"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_STATE"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_COUNTRY"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "MAILING_POSTCODE"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISOR_FORENAME"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISER_SURNAME"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISER_AGENT_NUMBER"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISER_CLIENT_NUMBER"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISER_EMAIL_ADDRESS"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "ADVISER_PHONE_NUMBER"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "LETTER_DATE"
                            If IsDate(dr(daCol.Ordinal)) Then
                                SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                            Else
                                bErrorFlag = True
                            End If
                        Case "DUE_DATE"
                            If IsDate(dr(daCol.Ordinal)) Then
                                SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                            Else
                                SQLValues += " '" & Now & "',"
                                'bErrorFlag = True
                            End If
                        Case "EXCEPTION_IDENTIFIER"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "NEW_PREMIUM_AMOUNT"
                            If IsNumeric(dr("CURRENT_PREMIUM_AMOUNT").ToString) Then
                                dr("CURRENT_PREMIUM_AMOUNT") = FuncCalc(dr("PAYMENT_FREQUENCY"), CDbl(Replace(Replace(dr("CURRENT_PREMIUM_AMOUNT").ToString, "$", ""), ",", "")))
                                '                                PremAmount = 
                                SQLValues += " " & dr("CURRENT_PREMIUM_AMOUNT").ToString & ","
                            Else
                                If IsNumeric(Replace(dr(daCol.Ordinal).ToString, "$", "")) Then
                                    SQLValues += " " & dr(daCol.Ordinal).ToString & ","
                                    dr("CURRENT_PREMIUM_AMOUNT") = dr(daCol.Ordinal).ToString
                                Else
                                    bErrorFlag = True
                                End If
                            End If
                            SQLValues += " 0.00,"
                            SQLValues += " 0.00,"

                        Case "SUPERANNUATION_INDICATOR"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "PAYMENT_FREQUENCY"
                            SQLValues += " '" & dr(daCol.Ordinal).ToString & "',"
                        Case "CURRENT_PREMIUM_AMOUNT"
                            If Trim(dr(daCol.Ordinal).ToString) = "" Then
                                SQLValues += " 0,"
                            Else
                                SQLValues += dr(daCol.Ordinal).ToString & ","
                            End If
                    End Select '

                    If daCol.ColumnName = "_RECORD_ID" Then
                        SQL += "[RECORD_ID] ,"
                    ElseIf daCol.ColumnName = "SYSTEM" Then
                        SQL += "[Account_SYSTEM] ,"
                    ElseIf daCol.ColumnName = "POLICY_TYPE" Then
                        '
                    ElseIf daCol.ColumnName = "Import Status" Then
                        '
                    ElseIf daCol.ColumnName = "NEW_PREMIUM_AMOUNT" Then
                        SQL += "[" & daCol.ColumnName & "] ,"
                        SQL += "[Retained] ,"
                        SQL += "[WriteOff] ,"
                    Else
                        SQL += "[" & daCol.ColumnName & "] ,"
                    End If

                    'SQLValues += " '" & dr(daCol.Ordinal) & "',"
                Next
                SQL += "[UserID] ,"
                SQLValues += " '" & RIBSState.Unallocated.ToString & "',"
                SQL = Left(SQL, Len(SQL) - 1) & ") "
                SQLValues = Left(SQLValues, Len(SQLValues) - 1) & ") "
                SQL += SQLValues
                ProgressBarUpdate = iCtr
                RunNumber = dr("CURRENT_RUN_NUMBER")
                Application.DoEvents()
                If bErrorFlag Then
                    dr("Import Status") = "Data Error"
                Else
                    dt2.Rows.Clear()
                    Dim lSql As String = "Select ribsID from RIBS WHERE [Record_ID] = '" & dr("_Record_ID") & "' " & _
                                                " AND POLICY_NUMBER = '" & dr("POLICY_NUMBER") & "' " & _
                                                " AND LETTER_DATE = " & dbDateFormat(dr("LETTER_DATE")) & " " & _
                                                " AND LETTER_CODE = '" & dr("LETTER_CODE").ToString & "'"
                    Dim cmd As New OleDbCommand(lSql, connection)
                    Dim Reader As OleDbDataReader
                    cmd.CommandText = lSql
                    Reader = cmd.ExecuteReader(CommandBehavior.SingleResult)

                    If Not Reader.Read() Then
                        If ValidCodes.Contains(LETTER_CODE) Then
                            command.CommandText = SQL
                            command.ExecuteNonQuery()
                            dr("Import Status") = "Imported"
                        Else
                            dr("Import Status") = "Skiped"
                        End If
                        
                    Else
                        SQL = "Update RIBS SET AMOUNT_DUE = " & dr("AMOUNT_DUE").ToString & _
                                ", NEW_PREMIUM_AMOUNT = " & dr("CURRENT_PREMIUM_AMOUNT").ToString & _
                                " WHERE [POLICY_NUMBER] = '" & dr("POLICY_NUMBER") & "' " & _
                                "   AND LETTER_CODE = '" & dr("LETTER_CODE") & "' " & _
                                "   AND CURRENT_RUN_NUMBER =  " & dr("CURRENT_RUN_NUMBER")
                        command.CommandText = SQL
                        command.ExecuteNonQuery()
                        dr("Import Status") = "Duplicate Found (Updated AMOUNT_DUE & NEW_PREMIUM_AMOUNT)"
                    End If
                    cmd.Dispose()
                    command.Dispose()
                    Application.DoEvents()
                End If
                Application.DoEvents()
            Next


            command.Connection = connection
            ProgressBarMaxValue = 10000
            For i As Int32 = 1 To ProgressBarMaxValue
                Application.DoEvents()
                ProgressBarUpdate = i
            Next
            Dim dtUpd As DataTable = GetDataTable("SELECT  Ribs.* " & _
                                            " FROM Ribs LEFT JOIN ActionHistory ON Ribs.RibsID = ActionHistory.RibsID  " & _
                                            " WHERE ActionHistory.ActionID is null")
            ProgressBarMaxValue = dtUpd.Rows.Count + 2
            iCtr = 1
            ProgressBarUpdate = iCtr

            For Each drx As DataRow In dtUpd.Rows
                iCtr += 1
                'For i As Int32 = 1 To dtUpd.Rows.Count - 1
                '    ProgressBarUpdate = i
                '    Application.DoEvents()
                'Next
                ProgressBarUpdate = iCtr
                'Application.DoEvents()
                SQL = "INSERT INTO ActionHistory (RibsID, [Action], [Note], ActionDate, [User], ContactMethod, [RibsState] )" & _
                                " VALUES ( " & drx("RibsID") & ",'" & _
                                RIBSState.Unallocated.ToString & "'," & _
                                "'Imported'," & _
                                "#" & Format(Now, "dd MMM yyyy") & "#," & _
                                "'" & RIBSState.Unallocated.ToString & " '," & _
                                "'" & RIBSState.Unallocated.ToString & "', " & _
                                "'" & RIBSState.Unallocated.ToString & "')"
             
                Command.CommandText = SQL
                Command.ExecuteNonQuery()

            Next

            ArchiveData()

        Catch ex As DataException
            Debug.Print(ex.Message)
            
            Throw ex
        Finally
            connection.close()
            connection.Dispose()
            command.Connection.Close()
            command.Dispose()

        End Try
        If Not IsNothing(dt.GetChanges()) Then dt.AcceptChanges()
        Return dt
    End Function

    Function ArchiveData() As Boolean
        Try
            Dim Sql1 As String
            Dim Sql2 As String
            Dim Sql3 As String
            Dim Sql4 As String
            Dim command As New OleDbCommand()
            Dim dbTran As OleDbTransaction
            Dim result As Object = Nothing
            Dim connection As New OleDbConnection(gConnectionString)

            connection.Open()
            dbTran = connection.BeginTransaction

            Sql1 = "INSERT INTO ActionHistory_Archive " & _
                  " SELECT * FROM ActionHistory " & _
                  " WHERE RIBSID IN (Select RIBSID FROM ActionHistory Where EntryDate <= " & dbDateFormat(DateAdd(DateInterval.Month, -12, Date.Now)) & ") " & _
                  " AND ActionHistory.ActionID NOT IN (SELECT ActionID FROM ActionHistory_Archive)"
            Sql2 = "INSERT INTO RIBS_Archive" & _
                  " SELECT * FROM RIBS" & _
                  " WHERE  RIBS.RIBSID  IN (SELECT RIBSID  FROM ActionHistory_Archive ) " & _
                  " AND RIBS.RIBSID NOT IN (SELECT RIBSID FROM RIBS_Archive) "

            Sql3 = "DELETE * FROM RIBS " & _
                  " WHERE RIBS.RIBSID IN  " & _
                  "   (SELECT top 200 RIBS.RIBSID   " & _
                  "    FROM RIBS_Archive INNER JOIN RIBS  " & _
                  "    ON RIBS_Archive.RIBSID =RIBS.RIBSID GROUP BY RIBS.RIBSID) "

            Sql4 = "DELETE * FROM ActionHistory " & _
                  " WHERE ActionID IN " & _
                  " (SELECT  TOP 200 ActionHistory.ActionID " & _
                  "        FROM ActionHistory_Archive INNER JOIN " & _
                  "        ActionHistory ON ActionHistory_Archive.ActionID = ActionHistory.ActionID GROUP BY ActionHistory.ActionID)"
            Try
                command.Connection = dbTran.Connection
                command.Transaction = dbTran

                command.CommandText = Sql1
                result = command.ExecuteScalar()

                command.CommandText = Sql2
                result = command.ExecuteScalar()

                For ictr As Int16 = 1 To oSysSetting.AchiveLoop
                    command.CommandText = Sql3
                    result = command.ExecuteScalar()

                    command.CommandText = Sql4
                    result = command.ExecuteScalar()
                Next

                dbTran.Commit()

            Catch ex As Exception
                dbTran.Rollback()
            End Try
            
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function FuncCalc(ByVal Freq As String, ByVal Payment As Double) As Double
        Try
            Freq = LCase(Freq)
            If InStr(Freq, "annual") > 0 Then
                Return Payment
            End If
            If InStr(Freq, "monthly") > 0 Then
                Return Payment * 12
            End If
            If InStr(Freq, "half yearly") > 0 Then
                Return Payment * 2
            End If
            If InStr(Freq, "quarterly") > 0 Then
                Return Payment * 4
            End If
            '
        Catch ex As Exception
            FuncCalc = 0
        End Try
    End Function

    Function CheckLetterCode(ByVal LetterCode As String) As Boolean
        Try
            Dim dt As New DataTable
            Dim Conn As New OleDbConnection(gConnectionString)
            Dim Adapter As New OleDbDataAdapter
            Dim cmd As New OleDbCommand
            Adapter.SelectCommand = New OleDbCommand("Select * from LETTER_CODE WHERE LETTER_CODE='" & LetterCode & "'", Conn)
            Adapter.Fill(dt)
            If dt.Rows.Count > 0 Then
                Return True
            Else
                Conn.Open()
                cmd.Connection = Conn
                cmd.CommandText = "INSERT INTO LETTER_CODE (LETTER_CODE) VALUES ('" & LetterCode & "')"
                cmd.ExecuteNonQuery()
                Return False
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Function GetLetterCodes() As String
        Try
            Dim dt As New DataTable
            Dim LetterCodes As String = ""
            Dim con As New OleDbConnection(gConnectionString)
            Dim cmd As New OleDbCommand("Select * from LETTER_CODE ", con)
            Dim Reader As OleDbDataReader

            con.Open()
            Reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
            While Reader.Read
                LetterCodes += Reader("LETTER_CODE") & ","
            End While
            Return LetterCodes
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
